package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
/*import forms.StudentFormDelete;
import forms.StudentFormInsert;
import forms.StudentFormUpdate;*/
import forms.ReviewerFormDelete;
import forms.ReviewerFormInsert;
import java.sql.*;

public class ReviewerModel {

    private static String reviewersStr = "SELECT COUNT(*) FROM reviewer";
    private static String fetchReviewerStr = "SELECT * FROM reviewer WHERE username = ?";
    private static String fetchAllReviewerStr = "SELECT * FROM reviewer";
    private static String insertStr = "INSERT INTO reviewer (username, password) VALUES (?, ?)";
    private static String insertToApplicantsStr = "INSERT INTO applicants (username, password, reviewerid) VALUES (?, ?, ?)";
    private static String insertToApplicantRolesStr = "INSERT INTO applicant_roles (username, role) VALUES (?, 'reviewer')";
    private static String deleteStr = "DELETE FROM reviewer WHERE reviewerid = ?";
    private static String deleteFromApplicantsStr = "DELETE FROM applicants WHERE reviewerid = ?";
    private static String deleteFromApplicantRolesStr = "DELETE FROM applicant_roles WHERE username = ?";
    private static String getReviewProcess = "SELECT * FROM review_process";
    private static String setReviewProcess = "UPDATE review_process SET review_begun = ?";
    private static String fetchAllIdStr = "SELECT reviewerid FROM reviewer";
    private static String insertReviewStr = "INSERT INTO review (pid, reviewerid) VALUES (?, ?)";
    private static String fetchUsernameById = "SELECT username FROM reviewer WHERE reviewerid = ?";
    private static String fetchIdByUsername = "SELECT reviewerid FROM reviewer WHERE username = ?";
    private static String workloadStr = "SELECT s.pid, s.first_name, s.middle_name, s.last_name, s.specialization, s.numreviews, s.decision FROM students s, reviewer r1, review r2 WHERE s.pid = r2.pid AND r2.reviewerid = r1.reviewerid AND r1.reviewerid = ? AND r2.graded = 0";
    private static String updateReviewStr ="UPDATE review set graded = 1, grade = ?, content = ? WHERE pid = ? AND reviewerid = ?";
    private static String incrementNotReviewedStr = "UPDATE reviewer SET notreviewed = (notreviewed + ?) WHERE reviewerid = ?";
    private static String incrementNumReviewedStr = "UPDATE reviewer SET numreviewed = (numreviewed + ?) WHERE reviewerid = ?";
    private static String alreadyGradedStr = "SELECT graded FROM review WHERE pid = ? AND reviewerid = ?";
    private static String suggestedReviewerStr = "SELECT DISTINCT reviewerid FROM review WHERE pid != ? AND reviewerid NOT IN (SELECT reviewerid FROM review WHERE pid = ?)";
    private static String hasReviewStr = "SELECT COUNT (*) FROM review r WHERE r.pid = ? AND r.reviewerid = ?";
    private static String reviewerAnalyticsStr = "SELECT username, numreviewed, notreviewed, reviewerid FROM reviewer ORDER BY username";
    private static String getAllUngradedStr = "SELECT DISTINCT s.pid, decision, first_name, middle_name, last_name, specialization, loc, university, discipline, grad_month, grad_year, gpa, title, country_citizenship, country_residence, street, city, state, zip, tel_code, area_code, num, numReviews FROM review r1, reviewer r2, students s, degrees d, address a WHERE r1.reviewerid = r2.reviewerid AND r1.pid = s.pid AND r2.reviewerid = ? AND r1.graded = 0 AND a.student = s.pid AND a.student = d.student AND s.pid = d.student ORDER BY pid";
    private static String getAllGradedStr = "SELECT DISTINCT s.pid, decision, first_name, middle_name, last_name, specialization, loc, university, discipline, grad_month, grad_year, gpa, title, country_citizenship, country_residence, street, city, state, zip, tel_code, area_code, num, numReviews FROM review r1, reviewer r2, students s, degrees d, address a WHERE r1.reviewerid = r2.reviewerid AND r1.pid = s.pid AND r2.reviewerid = ? AND r1.graded = 1 AND a.student = s.pid AND a.student = d.student AND s.pid = d.student ORDER BY pid";
    private static String applicationByDisciplineStr = "SELECT s.pid, decision, first_name, middle_name, last_name, specialization, loc, university, discipline, grad_month, grad_year, gpa, title, country_citizenship, country_residence, street, city, state, zip, tel_code, area_code, num FROM students s, degrees d, address a WHERE d.discipline = ? AND a.student = s.pid AND a.student = d.student AND s.pid = d.student ORDER BY pid";
    private static String applicationBySpecializationStr ="SELECT s.pid, decision, first_name, middle_name, last_name, specialization, loc, university, discipline, grad_month, grad_year, gpa, title, country_citizenship,  country_residence, street, city, state, zip, tel_code, area_code, num  FROM students s, degrees d, address a  WHERE s.specialization = ? AND a.student = s.pid AND a.student = d.student AND s.pid = d.student ORDER BY pid";
    
    final static int NULL = 0;
    final static int INTEGER = 4;
    final static int DOUBLE = 8;
    final static int STRING = 12;
    
    /**
     * fetchUsernameById
     * gets the username for the given id from the reviewer table
     * 
     * @param  reviewerid = the int holding the given id
     * @return a string containing the username of the reviewer with the given id
     * @throws DbException 
     */
    public static String fetchUsernameById(int reviewerid) throws DbException{
        String username = "";
        
        try {
            Connection conn = DbConnectionPool.getConnection();
            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(fetchUsernameById);
            pStmt.setInt(1, reviewerid);

            // Use the created statement to get count of the reviewers
            ResultSet reviewerInfo = pStmt.executeQuery();

            CachedRowSet crsReviewer = new CachedRowSetImpl();
            crsReviewer.populate(reviewerInfo);

            if(crsReviewer.next()){
                username = crsReviewer.getString("username");
            }
            
            reviewerInfo.close();
            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        
        return username;
    }
    
    /**
     * fetchIdByUsername
     * gets the id for the given username from the reviewer table
     * 
     * @param  username = the username of the given reviewer
     * @return an int containing the id of the given reviewer
     * @throws DbException 
     */
    public static int fetchIdByUsername(String username) throws DbException{
        int id = 0;
        
        try {
            Connection conn = DbConnectionPool.getConnection();
            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(fetchIdByUsername);
            pStmt.setString(1, username);

            // Use the created statement to get count of the reviewers
            ResultSet reviewerInfo = pStmt.executeQuery();

            CachedRowSet crsReviewer = new CachedRowSetImpl();
            crsReviewer.populate(reviewerInfo);

            if(crsReviewer.next()){
                id = crsReviewer.getInt("reviewerid");
            }
            
            reviewerInfo.close();
            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        
        return id;
    }
    
    /**
     * getReviewers
     * 
     * returns the total number of reviewers
     * 
     * @return number of reviewers in the DB
     * @throws DbException
     */
    public static int getNumReviewers() throws DbException {
        int result = 0;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(reviewersStr);

            // Use the created statement to get count of the reviewers
            ResultSet applicantInfo = pStmt.executeQuery();

            CachedRowSet crsReviewers = new CachedRowSetImpl();
            crsReviewers.populate(applicantInfo);

            if(crsReviewers.next()){
                result = crsReviewers.getInt(1);
            }
            
            applicantInfo.close();
            pStmt.close();
            conn.close();

            return result;
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
    
    /**
     * fetchReviewer
     * 
     * returns row of a particular reviewer
     * 
     * @param  reviewer   String containing the username of the reviewer
     * @return RowSet containing the reviewer's information
     * @throws DbException
     */
    public static CachedRowSet fetchReviewer(String reviewer) throws DbException {
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(fetchReviewerStr);
            pStmt.setString(1, reviewer);

            // Use the created statement to get count of the reviewers
            ResultSet reviewerInfo = pStmt.executeQuery();

            CachedRowSet crsReviewer = new CachedRowSetImpl();
            crsReviewer.populate(reviewerInfo);
            
            reviewerInfo.close();
            pStmt.close();
            conn.close();

            return crsReviewer;
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
    
    /**
     * fetchReviewer
     * 
     * returns row of a particular reviewer
     * 
     * @return RowSet containing all the reviewers' information
     * @throws DbException
     */
    public static CachedRowSet fetchAllReviewer() throws DbException {
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(fetchAllReviewerStr);

            // Use the created statement to get count of the reviewers
            ResultSet reviewerInfo = pStmt.executeQuery();

            CachedRowSet crsReviewer = new CachedRowSetImpl();
            crsReviewer.populate(reviewerInfo);
            
            reviewerInfo.close();
            pStmt.close();
            conn.close();

            return crsReviewer;
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
    
    /**
     * insertReviewer
     * 
     * inserts the given reviewer into the Database
     * 
     * @param reviewer   Reviewer form with the reviewer in question
     * @throws DbException
     */
    public static void insertReviewer (ReviewerFormInsert reviewer)
            throws DbException {
        int reviewerID = 0;
        String password = reviewer.getPassword();
        String encMD5pwd = org.apache.catalina.realm.RealmBase.Digest(password, "MD5", "ASCII");

        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the prepared statement and use it to
            // INSERT reviewer values INTO the reviewer table.
            PreparedStatement pStmt = conn.prepareStatement(insertStr, Statement.RETURN_GENERATED_KEYS);
            pStmt.setString(1, reviewer.getUsername());
            pStmt.setString(2, encMD5pwd);
            pStmt.executeUpdate();

            // gets reviewer id
            ResultSet rset = pStmt.getGeneratedKeys();
            // gets the last inserted item
            if(rset.next()){
              reviewerID = rset.getInt(1);
            }
            
            // Create the prepared statement and use it to
            // INSERT reviewer values INTO the applicants table.
            pStmt = conn.prepareStatement(insertToApplicantsStr);
            pStmt.setString(1, reviewer.getUsername());
            pStmt.setString(2, encMD5pwd);
            pStmt.setInt(3, reviewerID);
            pStmt.executeUpdate();
            
            // Create the prepared statement and use it to
            // INSERT reviewer values INTO the applicant_roles table.
            pStmt = conn.prepareStatement(insertToApplicantRolesStr);
            pStmt.setString(1, reviewer.getUsername());
            pStmt.executeUpdate();
            
            // Commit transaction
            conn.commit();

            pStmt.close();
            conn.close();
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
    
    /**
     * deletetReviewer
     * 
     * deletes the given reviewer into the Database
     * 
     * @param reviewer   Reviewer form with the reviewer in question to be deleted
     * @throws DbException
     */
    public static void deleteReviewer(ReviewerFormDelete reviewer)
            throws DbException {

        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the prepared statement and use it to
            // DELETE reviewers FROM the applicant_roles table.
            PreparedStatement pStmt = conn.prepareStatement(deleteFromApplicantRolesStr);
            pStmt.setString(1, fetchUsernameById(reviewer.getId()));
            pStmt.executeUpdate();
            
            // Create the prepared statement and use it to
            // DELETE reviewers FROM the applicant table.
            pStmt = conn.prepareStatement(deleteFromApplicantsStr);
            pStmt.setInt(1, reviewer.getId());
            pStmt.executeUpdate();
            
            // Create the prepared statement and use it to
            // DELETE reviewers FROM the reviewer table.
            pStmt = conn.prepareStatement(deleteStr);
            pStmt.setInt(1, reviewer.getId());
            pStmt.executeUpdate();

            // Commit transaction
            conn.commit();

            pStmt.close();
            conn.close();
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
    
    /**
     * reviewProcessHasBegun()
     * checks if the review process has begun
     * 
     * @param none
     * @return true if process has begun, otherwise false
     * @throws DbException 
     */
    public static boolean reviewProcessHasBegun() throws DbException{
        boolean result = false;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(getReviewProcess);

            // Use the created statement to get count of the reviewers
            ResultSet reviewProcess = pStmt.executeQuery();
            
            if(reviewProcess.next()){
                if(reviewProcess.getInt("review_begun") == 1){
                    result = true;
                }else{
                    result = false;
                }
            }

            reviewProcess.close();
            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        return result;
    }
    
    /**
     * startReviewProcess
     * switches the review process flag to zero indicating it is over
     * 
     * @param none
     * @return true if review process successfully starts
     * @throws DbException 
     */
    public static boolean startReviewProcess() throws DbException{
        boolean result = false;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(setReviewProcess);
            pStmt.setInt(1, 1);

            // updates the DB
            pStmt.executeUpdate();
            result = true;

            // Commit transaction
            conn.commit();

            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        return result;
    }
    
    /**
     * endReviewProcess
     * switches the review process flag to zero indicating it is over
     * 
     * @param none
     * @return true if review process successfully ends
     * @throws DbException 
     */
    public static boolean endReviewProcess() throws DbException{
        boolean result = false;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(setReviewProcess);
            pStmt.setInt(1, 0);

            // updates the DB
            pStmt.executeUpdate();
            result = true;

            // Commit transaction
            conn.commit();

            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        return result;
    }
    
    /**
     * fetchAllById
     * returns an arraylist of all the reviewer's ids
     * 
     * @return Arraylist holding all of reviewer's ids
     * @throws DbException 
     */
    public static ArrayList<Integer> fetchAllId() throws DbException{
        ArrayList<Integer> reviewers = new ArrayList<Integer>();
        
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(fetchAllIdStr);

            // Use the created statement to SELECT
            // the applicant attributes FROM the Student table.
            ResultSet applicantInfo = pStmt.executeQuery();

            CachedRowSet crsApplication = new CachedRowSetImpl();
            crsApplication.populate(applicantInfo);

            while(crsApplication.next()){
                reviewers.add(crsApplication.getInt("reviewerid"));
            }
            
            applicantInfo.close();
            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        
        return reviewers;
    }

    
    /**
     * insertReview
     * Creates a review that points to the given student and reviewer.
     * By default it is grade 0 and ungraded
     * 
     * @param reveiwerid = id of the reviewer to grade the application
     *        studentid = id of the student who owns the application
     * @return true if successful, otherwise false
     * @throws DbException 
     */
    public static boolean insertReview(int studentid, int reviewerid) throws DbException{
        boolean result = false;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(insertReviewStr);
            pStmt.setInt(1, studentid);
            pStmt.setInt(2, reviewerid);

            // updates the DB
            pStmt.executeUpdate();
            result = true;

            // Commit transaction
            conn.commit();

            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        return result;
    }
    
    /**
     * getWorkload
     * returns an arraylist of ungraded applications for the given reviewer
     * 
     * @param reviewerid   id of the reviewer in question
     * @return Arraylist holding all of the ungraded application's Ids
     * @throws DbException 
     */
    public static ArrayList<ArrayList> getWorkload (int reviewerid) throws DbException{
        ArrayList<ArrayList> applications = new ArrayList<ArrayList>();
        ArrayList student;
        
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(workloadStr);
            pStmt.setInt(1, reviewerid);

            // Use the created statement to SELECT
            // the applicant attributes FROM the Student table.
            ResultSet applicantInfo = pStmt.executeQuery();
            ResultSetMetaData rsetMeta = applicantInfo.getMetaData();
            int numCol = rsetMeta.getColumnCount();

            CachedRowSet crsApplication = new CachedRowSetImpl();
            crsApplication.populate(applicantInfo);

            // loop through all student applications
            while(crsApplication.next()){
                student = new ArrayList();
                // loop through every field in the application
                for(int i = 0; i < numCol; i++){
                    switch(rsetMeta.getColumnType(i+1)){
                    case NULL:
                        break;
                    case INTEGER:
                        student.add(crsApplication.getInt(i+1));
                        break;
                    case STRING:
                        student.add(crsApplication.getString(i+1));
                        break;
                    default:
                        student.add(crsApplication.getString(i+1));
                    }

                }
                applications.add(student);
            }
            
            applicantInfo.close();
            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        
        return applications;
    }
    
    /**
     * updateReview
     * 
     * updates the review of the given reviewer/student id with the given parameters
     * 
     * @param  grade    int holding the grade
     * @param  content  string holding the comments
     * @param  studentid   id of the student that is being reviewer
     * @param  reviewerid  id of the reviewer that is reviewing the student
     * @return true if successful otherwise false
     * @throws DbException 
     */
    public static boolean updateReview (int grade, String content, int studentid, int reviewerid) throws DbException{
        boolean result = false;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(updateReviewStr);
            pStmt.setInt(1, grade);
            pStmt.setString(2, content);
            pStmt.setInt(3, studentid);
            pStmt.setInt(4, reviewerid);

            // updates the DB
            pStmt.executeUpdate();
            result = true;
            
            // Commit transaction
            conn.commit();

            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        return result;
    }
    
    /**
     * incrementNotReviewed
     * 
     * increments the number of applications not reviewed by the given delta
     *
     * @param  reviewerid  id of the reviewer that is reviewing the student
     * @param  delta       amount to increment by
     * @return true if successful otherwise false
     * @throws DbException 
     */
    public static boolean incrementNotReviewed(int reviewerid, int delta) throws DbException {
        boolean result = false;
        int numReviewed = 0;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(incrementNotReviewedStr);
            pStmt.setInt(1, delta);
            pStmt.setInt(2, reviewerid);

            // updates the DB
            pStmt.executeUpdate();
            
            result = true;

            // Commit transaction
            conn.commit();

            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        return result;   
    }
    
    /**
     * incrementNumReviewed
     * 
     * increments the number of applications not reviewed by the given delta
     *
     * @param  reviewerid  id of the reviewer that is reviewing the student
     * @param  delta       amount to increment by
     * @return true if successful otherwise false
     * @throws DbException 
     */
    public static boolean incrementNumReviewed(int reviewerid, int delta) throws DbException {
        boolean result = false;
        int numReviewed = 0;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(incrementNumReviewedStr);
            pStmt.setInt(1, delta);
            pStmt.setInt(2, reviewerid);

            pStmt.executeUpdate();
            result = true;

            // Commit transaction
            conn.commit();

            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        return result;   
    }
    
    /**
     * alreadyGraded
     * 
     * checks if the review with the given reviewer and the given student
     * has already been graded
     * 
     * @param  reviewerid   the id for the reviewer grading the application
     * @param  studentid    the id for the student/owner of the applciation
     * @return  true or false if it has already been graded
     * @throws DbException 
     * 
     */
    public static boolean alreadyGraded(int reviewerid, int studentid) throws DbException{
        boolean result = false;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(alreadyGradedStr);
            pStmt.setInt(1, studentid);
            pStmt.setInt(2, reviewerid);

            // checks the DB if application is already graded
            ResultSet rset = pStmt.executeQuery();
            if(rset.next()){
                if(rset.getInt("graded")==1){
                    result = true;
                }
            }

            // Commit transaction
            conn.commit();

            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        return result;
    }

    /**
     * getSuggestedReviewers
     * 
     * returns all the reviewers who have not graded the given students
     * application
     * 
     * @param studentid  id of the given student
     * @return  ArrayList of all reviewers who have not graded this student's application
     * @throws DbException 
     */
    public static ArrayList<Integer> getSuggestedReviewers(int studentid) throws DbException {
        ArrayList<Integer> reviewers = new ArrayList<Integer>();
        
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(suggestedReviewerStr);
            pStmt.setInt(1, studentid);
            pStmt.setInt(2, studentid);

            // Use the created statement to SELECT
            // the applicant attributes FROM the Student table.
            ResultSet reviewerInfo = pStmt.executeQuery();

            CachedRowSet crsApplication = new CachedRowSetImpl();
            crsApplication.populate(reviewerInfo);

            // loop through all reviewers 
            while(crsApplication.next()){
               int reviewerid = crsApplication.getInt("reviewerid");
               reviewers.add(reviewerid);
            }
            
            reviewerInfo.close();
            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        
        return reviewers;
    }
    
    /**
     * hasReview
     * 
     * checks if the given reviewer already has a review for the given student
     * 
     * @param reviewerid  id for the reviewer
     * @param studentid   id for the student
     * @return  true if there exists a review, else false
     * @throws DbException 
     */
    public static boolean hasReview(int reviewerid, int studentid) throws DbException{
        boolean result = false;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(hasReviewStr);
            pStmt.setInt(1, studentid);
            pStmt.setInt(2, reviewerid);

            // checks the DB if review is already exists
            ResultSet rset = pStmt.executeQuery();
            if(rset.next()){
                // reivew exists
                if(rset.getInt(1) > 0){
                    result = true;
                }
            }

            // Commit transaction
            conn.commit();

            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        return result;
    }

    public static ResultSet getReviewerAnalytics() throws DbException {
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(reviewerAnalyticsStr);

            // Use the created statement to SELECT
            // the specializations from specializationS table
            ResultSet specializations = pStmt.executeQuery();

            CachedRowSet crsApplication = new CachedRowSetImpl();
            crsApplication.populate(specializations);

            specializations.close();
            pStmt.close();
            conn.close();

            return crsApplication;
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
    
    /**
     * getAllUngraded
     * 
     * returns all ungraded applications of a particular reviewer
     * 
     * @param  reviewerid   id of the reviewer 
     * @return RowSet containing all the ungraded applications
     * @throws DbException
     */
    public static CachedRowSet getAllUngraded(int reviewerId) throws DbException {
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(getAllUngradedStr);
            pStmt.setInt(1, reviewerId);

            // Use the created statement to get count of the reviewers
            ResultSet reviewerInfo = pStmt.executeQuery();

            CachedRowSet crsReviewer = new CachedRowSetImpl();
            crsReviewer.populate(reviewerInfo);
            
            reviewerInfo.close();
            pStmt.close();
            conn.close();

            return crsReviewer;
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
    
    /**
     * getAllGraded
     * 
     * returns all Graded applications of a particular reviewer
     * 
     * @param  reviewerid   id of the reviewer 
     * @return RowSet containing all the Graded applications
     * @throws DbException
     */
    public static CachedRowSet getAllGraded(int reviewerId) throws DbException {
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(getAllGradedStr);
            pStmt.setInt(1, reviewerId);

            // Use the created statement to get count of the reviewers
            ResultSet reviewerInfo = pStmt.executeQuery();

            CachedRowSet crsReviewer = new CachedRowSetImpl();
            crsReviewer.populate(reviewerInfo);
            
            reviewerInfo.close();
            pStmt.close();
            conn.close();

            return crsReviewer;
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }

    /**
     * getApplicationsByDiscipline
     * 
     * returns all the applications in a given discipline
     * 
     * @return ResultsSet containing all the applications in the specified discipline
     * @throws DbException
     */
    public static ResultSet getApplicationsByDiscipline(String discipline) throws DbException {
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(applicationByDisciplineStr);
            pStmt.setString(1, discipline);

            // Use the created statement to get count of the reviewers
            ResultSet reviewerInfo = pStmt.executeQuery();

            CachedRowSet crsReviewer = new CachedRowSetImpl();
            crsReviewer.populate(reviewerInfo);
            
            reviewerInfo.close();
            pStmt.close();
            conn.close();

            return crsReviewer;
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
    
    /**
     * getApplicationsBySpecialization
     * 
     * returns all the applications in a given specialziation
     * 
     * @return ResultsSet containing all the applications in the specified specialization
     * @throws DbException
     */
    public static ResultSet getApplicationsBySpecialization(String specialization) throws DbException {
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(applicationBySpecializationStr);
            pStmt.setString(1, specialization);

            // Use the created statement to get count of the reviewers
            ResultSet reviewerInfo = pStmt.executeQuery();

            CachedRowSet crsReviewer = new CachedRowSetImpl();
            crsReviewer.populate(reviewerInfo);
            
            reviewerInfo.close();
            pStmt.close();
            conn.close();

            return crsReviewer;
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
}
